package vip.xiaonuo.smzq.modular.xxgs.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.xxgs.entity.Xxgssj;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import vip.xiaonuo.smzq.modular.xxgs.entity.vo.JscZyclTj;
import vip.xiaonuo.smzq.modular.xxgs.entity.vo.XxgssjVo;

import java.util.Map;

/**
 * 信息公示_信息公示数据 Mapper
 *
 * @author sqq
 * @since 2024-07-19
 */
@Mapper
public interface XxgssjMapper extends BaseMapper<Xxgssj> {
    /**
     * 异常名单数量
     * @param orgId   行政区划名单
      * @param nd   年度
     * @return
     */
    @Select("select count(*) from xxgs_xxgssj where delflag=0 and sflrycml=1 and xzqdm like #{orgId} || '%' and lrrq like #{nd} || '%'")
    int getycmdCount(@Param("orgId") String orgId, @Param("nd") String nd);
    /**
     * 失信名单数量
     * @param orgId   行政区划名单
      * @param nd   年度
     * @return
     */
    @Select("select count(*) from xxgs_xxgssj where delflag=0 and sflrsxmd=1 and xzqdm like #{orgId} || '%' and lrrq like #{nd} || '%'")
    int getsxmdCount(@Param("orgId") String orgId, @Param("nd") String nd);


    /**
     * 信息公示_信息公示数据分页查询
     */
    @Select("select t.*,\n" +
            "case when t.sflrsxmd = 0 then '否' else '是' end as sflrsxmd_name,\n" +
            "case when t.sflrycml = 0 then '否' else '是' end as sflrycml_name\n" +
            "from xxgs_xxgssj t\n" +
            "${ew.customSqlSegment}\n" +
            "order by t.lrrq desc")
    Page<XxgssjVo> getPageList(IPage<XxgssjVo> page, @Param(Constants.WRAPPER) Wrapper<Xxgssj> queryWrapper);

    /**
     * 绿色矿山数量
     * @param orgId   行政区划名单
     * @param nd   年度
     * @return
     */
    @Select("select count(*) from xxgs_lsks where delflag=0  and xzqdm like #{orgId} || '%' and fbsj like #{nd} || '%'")
    int getlskcsCount(String orgId, String nd);

    @Select("WITH kszh AS (\n" +
            "\tSELECT\n" +
            "\t\thz.nd,\n" +
            "\t\thz.xzqdm,\n" +
            "\t\thz.kcdm,\n" +
            "\t\thz.tjdx,\n" +
            "\t\thz.kqbh,\n" +
            "\t\thz.jsdxmc,\n" +
            "\t\thz.sbbsm \n" +
            "\tFROM\n" +
            "\t\thisi_kshz hz \n" +
            "\tWHERE  ${sql}\n" +
            "\tGROUP BY\n" +
            "\t\thz.nd,\n" +
            "\t\thz.xzqdm,\n" +
            "\t\thz.fxzqdm,\n" +
            "\t\thz.kcdm,\n" +
            "\t\thz.tjdx,\n" +
            "\t\thz.kqbh,\n" +
            "\t\thz.jsdxmc,\n" +
            "\t\thz.sbbsm \n" +
            "\t),\n" +
            "\tzyl_cl AS (\n" +
            "\tSELECT\n" +
            "\t\tzy.nd,\n" +
            "\t\thz.xzqdm,\n" +
            "\t\tzy.jsldw,\n" +
            "\tSUM ( CASE WHEN zy.zycllx = 'ZS' THEN zy.nmbyjsl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'KX' THEN zy.nmbyjsl ELSE 0 END ) AS cl_js ,\n" +
            "\tSUM ( CASE WHEN zy.zycllx = 'TM' THEN zy.nmbyjsl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'KZ' THEN zy.nmbyjsl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'TD' THEN zy.nmbyjsl ELSE 0 END ) AS zyl_js,\n" +
            "\tSUM ( CASE WHEN zy.zycllx = 'ZS' THEN zy.nmbyksl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'KX' THEN zy.nmbyksl ELSE 0 END ) AS cl_ks ,\n" +
            "\tSUM ( CASE WHEN zy.zycllx = 'TM' THEN zy.nmbyksl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'KZ' THEN zy.nmbyksl ELSE 0 END ) + SUM ( CASE WHEN zy.zycllx = 'TD' THEN zy.nmbyksl ELSE 0 END ) AS zyl_ks \n" +
            "FROM\n" +
            "\thisi_ks_zyclhz zy\n" +
            "\tLEFT JOIN kszh hz ON hz.nd = zy.nd \n" +
            "\tAND hz.kqbh = zy.kqbh \n" +
            "\tAND hz.kcdm = zy.kcdm \n" +
            "\tAND hz.tjdx = zy.tjdx\n" +
            "\tLEFT JOIN kcl_cb08_xzqh xian ON hz.xzqdm = CAST ( xian.dm AS TEXT ) \n" +
            "WHERE ${sql}\n" +
            "GROUP BY\n" +
            "\tzy.nd,\n" +
            "\thz.xzqdm,\n" +
            "\tzy.jsldw ) \n" +
            "\t\n" +
            "\tSELECT\n" +
            "\tzc.xzqdm,\n" +
            "\txian.mc AS xzqmc,\n" +
            "\tzc.nd,\n" +
            "CASE\n" +
            "\t\tWHEN zc.jsldw IS NOT NULL AND zc.jsldw <> '' THEN\n" +
            "\t\tzc.zyl_js \n" +
            "\t\tELSE zc.zyl_ks \n" +
            "\tEND AS zyl,\n" +
            "CASE\n" +
            "\t\t\n" +
            "\t\tWHEN zc.jsldw IS NOT NULL AND zc.jsldw <> '' THEN\n" +
            "\t\tzc.cl_js ELSE zc.cl_ks \n" +
            "\tEND AS cl \n" +
            "FROM\n" +
            "\tzyl_cl zc\n" +
            "\tLEFT JOIN kcl_cb08_xzqh xian ON zc.xzqdm = CAST ( xian.dm AS TEXT ) \n" +
            "GROUP BY\n" +
            "\tzc.xzqdm,\n" +
            "\txian.mc,\n" +
            "\tzc.nd,\n" +
            "\tzc.jsldw,\n" +
            "\tzc.zyl_js,\n" +
            "\tzc.zyl_ks,\n" +
            "\tzc.cl_js,\n" +
            "\tzc.cl_ks;")
    JscZyclTj getZycltj(@Param(value ="sql") String sql);
}
